from fastapi import Depends, APIRouter
from pydantic import BaseModel
from util import ip
import connectorx as cx

from api.not_shipped.query import UpdateScanDetail, GetScanDetail
from util.JWT import get_current_user

notshipped = APIRouter()


# 添加用户
class queryType(BaseModel):
    con: str = ""
    item: str = ""
    caseno: str = ""
    currentPage4: int = 1
    pageSize4: int = 10
    flag: int = 0


@notshipped.post("/nsquery")
async def query(data: queryType, id: dict = Depends(get_current_user)):
    sql = f"""
    SELECT Old_ScanDetailID AS ID,ContractNo, Item, CaseNo, Qty, ReturnQty
    FROM Old_ScanDetail
    WHERE PackageQtyStatus > 7 AND PackageQtyStatus < 9 AND ContractNo LIKE '%{data.con}%' AND Item LIKE'%{data.item}%' AND CaseNo LIKE '%{data.caseno}%'
    UNION ALL
    SELECT B.RecodeID AS ID,B.ContractNo, B.Item, B.CaseNo, B.Qty, B.ReturnQty
    FROM Contract AS A
    LEFT JOIN ScanDetail AS B ON A.ContractNO = B.ContractNO
    WHERE A.PackageQtyStatus > 7 AND A.PackageQtyStatus < 9
    AND B.ContractNo LIKE '%{data.con}%' AND B.Item LIKE'%{data.item}%' AND B.CaseNo LIKE '%{data.caseno}%'
    """
    # df = GetScanDetail(sql)
    df = cx.read_sql(f"mssql://sa:cj126414.@{ip}/AnJiServer",
                        sql,
                        return_type="polars"
                        )
    # df = table.to_pandas()

    if df is None:
        return {
            "code": 204,
            "status": "no",
            "msg": "没有内容",
            "total": 0,
            "data": {}
        }
    # df.fillna('', inplace=True)
    total = len(df)
    if data.flag == 0:
        offset = (data.currentPage4 - 1) * data.pageSize4
        # df = df.iloc[offset: offset + data.pageSize4]
        df=df.slice(offset, data.pageSize4)

    # 将DataFrame转换为字典列表
    # dict_list = df.to_dict(orient='records')
    dict_list = df.to_dicts()
    # 将字典列表转换为指定格式的JSON字符串
    return {
        "code": 200,
        "status": "ok",
        "msg": "success",
        "total": total,
        "data": dict_list
    }


# 退库
class reType(BaseModel):
    ID: int
    yes_qty: int


@notshipped.post("/nsreturn")
async def nsreturn(data: reType, id: dict = Depends(get_current_user)):
    sql = ''
    if data.ID >= 50000000:
        sql = f"""
        UPDATE Old_ScanDetail SET ReturnQty=ReturnQty+{data.yes_qty} WHERE Old_ScanDetailID={data.ID}
        """
    else:
        sql = f"""
        UPDATE ScanDetail SET ReturnQty=ReturnQty+{data.yes_qty} WHERE RecodeID={data.ID}
        """

    if UpdateScanDetail(sql):
        return {
            "code": 200,
            "status": "ok",
            "msg": "退库成功",
        }
    else:
        return {
            "code": 500,
            "status": "no",
            "msg": "退库失败",
        }


# 发货
class shType(BaseModel):
    ContractNo: str


@notshipped.post("/nsship")
async def nsship(data: shType, id: dict = Depends(get_current_user)):
    sql = f"""
    UPDATE Old_ScanDetail SET PackageQtyStatus=9 WHERE ContractNo='{data.ContractNo}'
    """
    if UpdateScanDetail(sql):
        return {
            "code": 200,
            "status": "ok",
            "msg": "发货成功",
        }
    else:
        return {
            "code": 500,
            "status": "no",
            "msg": "发货失败",
        }
